﻿using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace TonyNet4V1.SQLServer
{
	/// <summary>
	/// SQL Server数据层基础类
	/// </summary>
	public class DataLayer : DataBase
	{
		#region 属性
		
		/// <summary>
		/// 数据库操作类
		/// </summary>
		/// <![CDATA[2014-6-20 15:57:36]]>
		protected DBOperation clsDB;

		/// <summary>
		/// 表结构获得控制类
		/// </summary>
		/// <![CDATA[2014-6-20 15:57:46]]>
		protected TableStructureComponent clsTableStructureComp;

		#endregion 属性

		#region 构造函数

		public DataLayer(string strTableName, System.Web.UI.Page page)
			: base(page)
		{
			clsDB = new DBOperation(page);
			clsTableStructureComp = new TableStructureComponent(page);

			this.strTableName = strTableName;
			if (InitTableStructure())
			{
				//_strSQL = "SELECT * FROM " + strTableName + " WHERE " + listStructure[this.nStatusNumber].strName + " = '" + _clsConfig.nNormal.ToString() + "'";
				_strSQL = "SELECT * FROM " + strTableName;
			}
			else
			{
				_strSQL = "SELECT * FROM " + strTableName;
			}
		}

		public DataLayer(string strTableName, int nIDNumber, int nStatusNumber, System.Web.UI.Page page)
			: this(strTableName, page)
		{
			this.nIDNumber = nIDNumber;
			this.nStatusNumber = nStatusNumber;

			clsDB.page = this.page;
		}

		#endregion 构造函数

		/// <summary>
		/// 初始化表结构链表
		/// </summary>
		public Boolean InitTableStructure()
		{
			listStructure = clsTableStructureComp.GetTableStructure(strTableName);
			if (listStructure.Count > 0)
			{
				return true;
			}
			else
			{
				ShowMessageAlert.ShowMessage(page, "表结构链表构建失败！");
				return false;
			}
		}

		#region 添加、修改、删除

		/// <summary>
		/// 添加记录到数据库
		/// </summary>
		public override bool Add()
		{
			int i;
			string strTemp;
			string strSQL;
			Structs.TableStructure clsError = new Structs.TableStructure();

			if (CheckLength(ref clsError))
			{
				//listStructure[nStatusNumber].strValue = _clsConfig.nNormal.ToString();
				strSQL = "INSERT INTO " + strTableName + " (";
				i = 0;
				strTemp = "";
				while (i < listStructure.Count)
				{
					if (i != nIDNumber)
					{
						strTemp = strTemp + listStructure[i].strName + ", ";
					}
					i = i + 1;
				}

				strTemp = CommonFunction.ExceptString(", ", strTemp);
				strSQL = strSQL + strTemp + ") VALUES (";
				strTemp = "";
				i = 0;
				while (i < listStructure.Count)
				{
					if (i != nIDNumber)
					{
						strTemp = strTemp + "'" + listStructure[i].strValue + "', ";
					}
					i = i + 1;
				}
				strTemp = CommonFunction.ExceptString(", ", strTemp);
				strSQL = strSQL + strTemp + ")";

				if (clsDB.ExecuteSQL(strSQL) > 0)
				{
					return true;
				}
				else
				{
					return false;
				}
			}
			else
			{
				ShowMessageAlert.ShowMessage(page, clsError.strName + "值长度错！");
				return false;
			}
		}
		////////////////////////////////////////////////////////////////////////////////////////////////////

		/// <summary>
		/// 修改一条记录
		/// </summary>
		/*******************************************************************
		 * * 功    能：	修改一条记录
		 * * 参    数：
		 * * 返 回 值：
		 * * 作    者： Tony Mucor
		 * * 创建日期： 2013-9-10 15:20:08
		 * *******************************************************************/
		public override bool Update()
		{
			int i;
			string strTemp;
			string strSQL;
			Structs.TableStructure clsError = new Structs.TableStructure();

			if (CheckLength(ref clsError))
			{
				//listStructure[nStatusNumber].strValue = _clsConfig.nNormal.ToString();
				i = 0;
				strTemp = "";
				while (i < listStructure.Count)
				{
					if (i != nIDNumber)
					{
						strTemp = strTemp + listStructure[i].strName + " = " + "'" + listStructure[i].strValue + "', ";
					}
					i = i + 1;
				}
				strTemp = CommonFunction.ExceptString(", ", strTemp);
				//strSQL = "UPDATE " + strTableName + " SET " + strTemp + " WHERE " + listStructure[nIDNumber].strName + " = '" + listStructure[nIDNumber].strValue + "' AND " + listStructure[nStatusNumber].strName + " = '" + listStructure[nStatusNumber].strValue + "'";
				strSQL = "UPDATE " + strTableName + " SET " + strTemp + " WHERE " + listStructure[nIDNumber].strName + " = '" + listStructure[nIDNumber].strValue + "'";
				if (clsDB.ExecuteSQL(strSQL) > 0)
				{
					return true;
				}
				else
				{
					return false;
				}
			}
			else
			{
				ShowMessageAlert.ShowMessage(page, clsError.strName + "值长度错！");
				return false;
			}
		}

		/// <summary>
		/// 删除一条记录
		/// </summary>
		/*******************************************************************
		 * * 功    能：	删除一条记录
		 * * 参    数：
		 * * 返 回 值：
		 * * 作    者： Tony Mucor
		 * * 创建日期： 2013-9-10 15:22:33
		 * *******************************************************************/
		public override bool Delete()
		{
			string strSQL;

			listStructure[nStatusNumber].strValue = _clsConfig.nDelete.ToString();
			strSQL = "UPDATE " + strTableName + " SET " + listStructure[nStatusNumber].strName + " = '" + listStructure[nStatusNumber].strValue + "' WHERE " + listStructure[nIDNumber].strName + " = '" + listStructure[nIDNumber].strValue + "'";
			if (clsDB.ExecuteSQL(strSQL) > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除所有记录
		/// </summary>
		/// <![CDATA[2014-6-4 11:09:53]]>
		/// <returns></returns>
		public override bool DeleteAll()
		{
			string strSQL;

			listStructure[nStatusNumber].strValue = _clsConfig.nDelete.ToString();
			strSQL = "UPDATE " + strTableName + " SET " + listStructure[nStatusNumber].strName + " = '" + listStructure[nStatusNumber].strValue + "'";
			if (clsDB.ExecuteSQL(strSQL) > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		#endregion  添加、修改、删除

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>
		/// 根据SQL语句查询
		/// </summary>
		/// <param name="strSQL"></param>
		/// <returns></returns>
		public override DataTable Query(string strSQL)
		{
			return clsDB.Query(strSQL) ;
		}
		////////////////////////////////////////////////////////////////////////////////////////////////////

		////////////////////////////////////////////////////////////////////////////////////////////////////
		//
		/// <summary>
		/// 根据查询条件实体查询
		/// </summary>
		/// <param name="clsQuery"></param>
		/// <returns></returns>
		public override DataTable Query(QueryEntityBase clsQuery, List<Structs.QueryData> listQueryData)
		{
			string strSQL = "";
			string strField = "";

			strSQL = _strSQL;

			if (listQueryData.Count > 0)
			{
				strSQL = strSQL + " WHERE ";
			}

			for (int i = 0; i < listQueryData.Count; i++)
			{

				if (listQueryData[i].bDateTime)
				{
					strField = "CAST (" + listQueryData[i].strFieldName + " AS DATETIME)";
				}
				else
				{
					strField = listQueryData[i].strFieldName;
				}

				if (listQueryData[i].strType == "int")
				{
					if (clsQuery.GetType().GetField(listQueryData[i].strVarName).GetValue(clsQuery).ToString() != _clsConfig.nQueryConditionIntAll.ToString())
					{
						strSQL = strSQL + strField + " " + listQueryData[i].strQuery + " '" + clsQuery.GetType().GetField(listQueryData[i].strVarName).GetValue(clsQuery).ToString() + "' AND ";
					}
				}

				if (listQueryData[i].strType == "float")
				{
					if (clsQuery.GetType().GetField(listQueryData[i].strVarName).GetValue(clsQuery).ToString() != _clsConfig.nQueryConditionIntAll.ToString())
					{
						strSQL = strSQL + strField + " " + listQueryData[i].strQuery + " '" + clsQuery.GetType().GetField(listQueryData[i].strVarName).GetValue(clsQuery).ToString() + "' AND ";
					}
				}

				if (listQueryData[i].strType == "string")
				{
					if (clsQuery.GetType().GetField(listQueryData[i].strVarName).GetValue(clsQuery).ToString() != _clsConfig.strQueryConditionStringAll)
					{
						if (listQueryData[i].strQuery.ToUpper() == "IN")
						{
							strSQL = strSQL + strField + " " + listQueryData[i].strQuery + " (" + clsQuery.GetType().GetField(listQueryData[i].strVarName).GetValue(clsQuery).ToString() + ") AND ";
						}
						else
						{
							if (listQueryData[i].strQuery.ToUpper() == "LIKE")
							{
								strSQL = strSQL + strField + " " + listQueryData[i].strQuery + " '%" + clsQuery.GetType().GetField(listQueryData[i].strVarName).GetValue(clsQuery).ToString() + "%' AND ";
							}
							else
							{
								strSQL = strSQL + strField + " " + listQueryData[i].strQuery + " '" + clsQuery.GetType().GetField(listQueryData[i].strVarName).GetValue(clsQuery).ToString() + "' AND ";
							}
						}
					}
				}
			}

			//if (_strOrder == "")
			//{
			//        _strOrder = BulidOrder();
			//}

			strSQL = strSQL + " 1 = '1' ";

			strSQL = strSQL + _strOrder;

			return clsDB.Query(strSQL);
		}
		////////////////////////////////////////////////////////////////////////////////////////////////////

		////////////////////////////////////////////////////////////////////////////////////////////////////
		//
		/// <summary>
		/// 查询最新插入记录的ID号
		/// </summary>
		/// <returns></returns>
		//public int QueryNewestInsertID()
		//{
		//        string strSQL;
		//        DataTable dt;

		//        strSQL = "SELECT TOP 1 * FROM " + strTableName + " WHERE " + listStructure[nStatusNumber].strName + " = " + _clsConfig.nNormal.ToString() + " ORDER BY " + listStructure[nIDNumber].strName + " DESC";
		//        dt = clsDB.Query(strSQL);
		//        if (dt != null)
		//        {
		//                return Convert.ToInt32(dt.Rows[0][nIDNumber]);
		//        }
		//        else
		//        {
		//                return 1;
		//        }
		//}
		////////////////////////////////////////////////////////////////////////////////////////////////////

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>
		/// 检查添加、修改的数据是否符合数据库大小的要求
		/// </summary>
		private Boolean CheckLength(ref Structs.TableStructure clsErrorEntity)
		{
			Boolean bReturn = true;
			foreach (Structs.TableStructure clsEntity in listStructure)
			{
				if (clsEntity.bCheckLength)
				{
					if (clsEntity.strValue.Length > clsEntity.nLength)
					{
						bReturn = false;
						clsErrorEntity = clsEntity;
						break;
					}
				}
			}
			return bReturn;
		}
		////////////////////////////////////////////////////////////////////////////////////////////////////

		/// <summary>
		/// 获得刚刚插入的记录
		/// </summary>
		/// <returns></returns>
		public override DataRow GetNewest()
		{
			string strSQL;
			DataTable dt;

			strSQL = "SELECT TOP 1 * FROM "+strTableName+" ORDER BY "+listStructure[nIDNumber].strName+" DESC";
			dt = Query(strSQL);
			if (dt != null)
			{
				if (dt.Rows.Count > 0)
				{
					return dt.Rows[0];
				}
				else
				{
					return null;
				}
			}
			else
			{
				return null;
			}
		}
	}
}